/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package monitor;

/**
 *
 * @author FrostBiteX77
 */

import java.sql.*;

public class QHandler {
    
    String query = "";
    
    public QHandler(){
        
        DBManager con = new DBManager();
        con.Connect();
}
    
    public void addTutor(String idNum, String fName, String mName, String lName, char gender, String degreeProgram, String CGPA, String emailAdd, String remark){
        
        query = "INSERT INTO `ptsmonitoringdb`.`tutor` (`idNum`, `fName`, `mName`, `lName`, `gender`," +
                "`degreeProgram`, `CGPA`, `emailAdd`, `remark`)" + 
                "VALUES ('"+ idNum + "', '" + fName + "', '" + mName + "', '" + lName + "', '" + gender + "', '" +
                degreeProgram + "', '" + CGPA + "', '" + emailAdd + "', '" + remark + "');";       
    }
    
    public void addTutee(String idNum, String fName, String mName, String lName, char gender, String degreeProgram, String CGPA, String tutorId, String emailAdd, String remark){
        
        query = "INSERT INTO `ptsmonitoringdb`.`tutee` (`idNum`, `fName`, `mName`, `lName`, `gender`," +
                "`degreeProgram`, `CGPA`, `tutor_idNum`, `emailAdd`, `remark`)" + 
                "VALUES ('"+ idNum + "', '" + fName + "', '" + mName + "', '" + lName + "', '" + gender + "', '" +
                degreeProgram + "', '" + CGPA + "', '" + tutorId + "', '" + emailAdd + "', '" + remark + "');";       
        
        
    }
    
    public void addSchedule(String tutorID, String tuteeID, String schedDay, String schedTime, String course){
        
        query = "INSERT INTO `ptsmonitoringdb`.`tutoringsched` (`tutorID`, `tutorName`, `tuteeID`, `tuteeName`, `schedDay`, `schedTime`, `course`)"
                + "VALUES ('" + tutorID +"', (select concat(lName, ' ', fName, ' ', mName) from tutor where idNum like '" + tutorID 
                + "') , '" + tuteeID +"', (select concat(lName, ' ', fName, ' ', mName) from tutee where idNum like '" + tuteeID + "'), '" + schedDay +"', '" + schedTime +"', '" + course +"');";
    }
    
    public void addContactTutor(String idNum, String contactNum){
        
        query = "INSERT INTO `ptsmonitoringdb`.`tutorcontactnumber` (`tutorID`, `contactNum`)" + "VALUES ('" + idNum  + "', '" + contactNum + "');";

    }
    
    public void addContactTutee(String idNum, String contactNum){
        
        query = "INSERT INTO `ptsmonitoringdb`.`tuteecontactnumber` (`tuteeID`, `contactNum`)" + "VALUES ('" + idNum  + "', '" + contactNum + "');";
    }
    
    public void addCourse(String idNum, String course){
        
        query = "INSERT INTO `ptsmonitoringdb`.`course_has_tutor` (`Course_courseCode`, `tutor_idNum`) VALUES ('" + course + "', '" + idNum  +"')";

    }
    
    public void addCourseGrade(String idNum, String surName, String course, String grade){
        
        query = "INSERT INTO `ptsmonitoringdb`.`course_has_tutor` (`Course_courseCode`, `tutor_surName`, `tutor_idNum`, `grade`) VALUES ('" + course + "', '" + surName + "', '" + idNum  +"', '" + grade + "')";

    }
    
    public void addCourseTutee(String idNum, String course){
        
         query = "INSERT INTO `ptsmonitoringdb`.`course_has_tutee` (`Course_courseCode`, `tutee_idNum`) VALUES ('" + course + "', '" + idNum  +"')";
        
    }
    
    public void viewTutor(){
        
        query = "SELECT idNum, concat(lName, ',', ' ', fName, ' ', mName), degreeProgram from tutor order by lName asc"; 
        
        
    }
    
    public void viewTutorGrade(){
        
        query = "SELECT * From course_has_tutor order by Course_courseCode asc";
    }
    
    public void viewTutee(){
        
        query = "SELECT idNum, concat(lName, ',', ' ', fName, ' ', mName), degreeProgram from tutee order by lName asc";    

    }
    
    public void getTutor(String course){
        
        query = "SELECT idNum as ID_Number, concat(lName, ',', ' ', fName, ' ', mName) as Tutor_Name FROM tutor, course_has_tutor where "
                + "Course_courseCode like '" + course + "' && tutor.IDnum = course_has_tutor.tutor_IDnum";
        
        
    }
    
      public void getTutorInfo(String tutorID){
        
        query = "SELECT * FROM tutor where idNum = '" + tutorID + "'";
        
    }
      
      public void getTutorContact(String tutorID){
        
        query = "SELECT contactNum FROM tutorcontactnumber where tutorID like '" + tutorID + "'";
    }
    
    
    public void getTuteeInfo(String tuteeID){
        
        query = "SELECT * FROM tutee where idNum = '" + tuteeID + "'";
        
    }
    
    public void getTuteeContact(String tuteeID){
        
        query = "SELECT contactNum FROM tuteecontactnumber where tuteeID like '" + tuteeID + "'";
    }
    
    public void getSchedInfo(String SchedID){
        
        query = "SELECT * FROM tutoringsched where schedNum like '" + SchedID + "'";
    }
    
    public void getSchedInfoTutee(String TuteeID){
        
        query = "SELECT * FROM tutoringsched where tuteeID = '" + TuteeID + "'";
    }
    
       
    public void getSchedInfoTutor(String TutorID){
        
        query = "SELECT * FROM tutoringsched where tutorID = '" + TutorID + "'";
    }
    
    
    public void getIdCourseTutor(String TutorID){
        
        query = "SELECT * FROM course_has_tutor where tutor_idNum = '" + TutorID + "' ";
    }
    
    public void selectIdNum(String course){
        
        query = "SELECT tutor_idNum FROM course_has_tutor where Course_courseCode like '" + course + "';";
    }
    
     public void selectIdNumTutee(){
        
        query = "SELECT idNum, lName FROM tutee order by lName asc";
    }
     
        
    
    public void selectIdNumTutor(){
        
        query = "SELECT idNum, lName FROM tutor order by lName asc";
    }
     
    public void selectIdSched(){
        
        query = "SELECT schedNum FROM tutoringsched";
    }
     
    public void viewCourse(){
        
        query = "SELECT DISTINCT Course_courseCode from course_has_tutor";
    }
    
    public void viewSched(){
        
        query = "Select schedNum, tutorName, tuteeName, schedDay, schedTime, course FROM tutoringsched";
    }
    
    public void updateTuteeInfo(String oldIdNum, String newIdNum, String fName, String mName, String lName, String degreeProgram, String CGPA, String emailAdd, String remark){
     
        query = "UPDATE `ptsmonitoringdb`.`tutee` SET `idNum`='" + newIdNum +"', `fName`='"+ fName +"', `mName`='" + mName + "', "
                + "`lName`='"+ lName +"', `degreeProgram`='" + degreeProgram + "', `CGPA`='" + CGPA + "', `emailAdd`='" + emailAdd + "', `remark`='" + remark + "' WHERE `idNum`='"+ oldIdNum +"';";
    
    }
    
    public void getTutoringSchedID(String idNum){
        
        query = "select schedNum from tutoringsched where tutorID = '" + idNum + "'";
 
    }
    
    public void getTutoringSchedIDtutee(String idNum){
        
        query = "SELECT schedNum FROM tutoringsched where tuteeID = '" + idNum + "'";
 
    }
    
    public void getTutorCourse(String tutorID){
        
        query = "SELECT * FROM course_has_tutor where tutor_idNum = '" + tutorID + "'";
    }
    
    public void updateTuteeContact(String oldIdNum, String newIdNum, String contactNum){
        
        query = "UPDATE `ptsmonitoringdb`.`tuteecontactnumber` SET `tuteeID`='"+ newIdNum +"', `contactNum`='"+ contactNum +"' WHERE `tuteeID`='" + oldIdNum +  "';";

    }
     public void updateTutorInfo(String oldIdNum, String newIdNum, String fName, String mName, String lName, String degreeProgram, String CGPA, String emailAdd, String remark){
    query = "UPDATE `ptsmonitoringdb`.`tutor` SET `idNum`='" + newIdNum + "', `fName`='"+ fName +"', `mName`='" + mName + "', "
            + "`lName`='" + lName + "', `degreeProgram`='" + degreeProgram + "', `CGPA`='"+ CGPA +"', `emailAdd`='" + emailAdd + "', `remark`='" + remark + "' WHERE `idNum`='" + oldIdNum + "';";

     }
     
     public void updateTutorContact(String oldIdNum, String newIdNum, String contactNum){
        
        query = "UPDATE `ptsmonitoringdb`.`tutorcontactnumber` SET `tutorID`='"+ newIdNum +"', `contactNum`='"+ contactNum +"' WHERE `tutorID`='" + oldIdNum +  "';";

    }
     
     public void updateTutorCourse(String oldIdNum, String newIdNum, String course){
         
        query = "UPDATE `ptsmonitoringdb`.`course_has_tutor` SET `tutor_idNum`='" + newIdNum + "' where tutor_idNum = '" + oldIdNum + "' and Course_courseCode = '" + course + "'";
 
     }
    
    
     public void updateSchedInfo(String tutorID, String tutorName, String tuteeID, String tuteeName, String schedID, String schedDay, String schedTime, String course){
         
         query = "UPDATE `ptsmonitoringdb`.`tutoringsched` SET `tutorID`='" + tutorID + "', `tutorName`='" + tutorName + "', `tuteeID`='" + tuteeID + "', "
                 + "`tuteeName`='" + tuteeName + "', `schedDay`='" + schedDay + "', `schedTime`='" + schedTime + "', `course`='" + course + "' WHERE `schedNum`='" + schedID + "';";

     }
     
     public void updateSchedInfo2(String schedID, String tutorID, String tutorName){
         
          query = "UPDATE `ptsmonitoringdb`.`tutoringsched` SET `tutorID`='" + tutorID + "', `tutorName`='" + tutorName + "' WHERE `schedNum`='" + schedID + "';";
     }
     
      public void updateSchedInfo3(String schedID, String tuteeID, String tuteeName){
         
          query = "UPDATE `ptsmonitoringdb`.`tutoringsched` SET `tuteeID`='" + tuteeID + "', `tuteeName`='" + tuteeName + "' WHERE `schedNum`='" + schedID + "';";
     }
      
     public void deleteTutorInfo(String tutorID){
         
         query = "DELETE FROM `ptsmonitoringdb`.`tutor` WHERE `idNum`= '" + tutorID + "'";
     }
     
     public void deleteTutorContact(String tutorID){
         
         query = "DELETE FROM `ptsmonitoringdb`.`tutorcontactnumber` WHERE `tutorID`= '" + tutorID + "'";
     }
    
     public void deleteTutorCourse(String tutorID, String course){
         
         query = "DELETE FROM `ptsmonitoringdb`.`course_has_tutor` WHERE `tutor_idNum`='" + tutorID + "' and`Course_courseCode`='" + course + "'";
     }
     
     public void deleteTuteeInfo(String tuteeID){
         
         query = "DELETE FROM `ptsmonitoringdb`.`tutee` WHERE `idNum`= '" + tuteeID + "'";
     }
     
     public void deleteTuteeContact(String tuteeID){
         
         query = "DELETE FROM `ptsmonitoringdb`.`tuteecontactnumber` WHERE `tuteeID`= '" + tuteeID + "'";
     }

     public void deleteSched(String schedID){
         
         query = "DELETE FROM `ptsmonitoringdb`.`tutoringsched` WHERE `schedNum`='" + schedID + "'";
     }
     
     public void viewTotalTutor(){
         
         query = "select count(*) as total from tutor";
     }
       
     public void viewTotalTutee(){
         
         query = "select count(*) as total from tutee";
     }
     
     public void viewTotalAssignments(){
         query = "select count(*) as total from tutoringsched";
     }
     
     
}
